﻿using Dapper;
using Domain.Entity;
using Domain.Entity.ord;
using Domain.IRepository;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EFCore.Repository
{
    public class SaleReturn_hRepository : ISaleReturn_hRepository
    {
        //连接字符串
        string con = "Data Source=121.37.93.71;Initial Catalog=CSSL_ZCTWMS;User ID=sa;Password=geili@2025;Encrypt=False";


        //添加
        public async Task<int> AddSaleReturn_h(SaleReturn_h saleReturn_H)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"insert into [dbo].[ord_sale_return_h](salet_code, salet_date, sale_id, sale_code, cust_id, cust_name, cust_linkman, cust_linkphone, sale_depId, sale_depName, sale_userId, sale_userName, salet_reason, salet_categories, salet_num, salet_total, salet_instockOK_num, salet_instockNO_num, salet_status, create_name, create_time, remark)
                                values('{saleReturn_H.salet_code}','{saleReturn_H.salet_date}',{saleReturn_H.sale_id},'{saleReturn_H.sale_code}',{saleReturn_H.cust_id},'{saleReturn_H.cust_name}','{saleReturn_H.cust_linkman}','{saleReturn_H.cust_linkphone}',{saleReturn_H.sale_depId},'{saleReturn_H.sale_depName}',{saleReturn_H.sale_userId},'{saleReturn_H.sale_userName}','{saleReturn_H.salet_reason}',{saleReturn_H.salet_categories},{saleReturn_H.salet_num},'{saleReturn_H.salet_total}',{saleReturn_H.salet_instockOK_num},{saleReturn_H.salet_instockNO_num},{saleReturn_H.salet_status},'{saleReturn_H.create_name}','{saleReturn_H.create_time}','{saleReturn_H.remark}');
                                SELECT CAST(SCOPE_IDENTITY() AS INT);";
                return await sqlConnection.ExecuteScalarAsync<int>(sql);
            }
        }


        //审核
        public async Task<int> ApproverSaleReturn_h(int saletid, int status, string approvername, string approvertime)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"update [dbo].[ord_sale_return_h] set salet_status={status},approver_name='{approvername}',approver_time='{approvertime}' where salet_id={saletid}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //删除
        public async Task<int> DeleteSaleReturn_h(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"update [dbo].[ord_sale_return_h] set is_delete=1 where salet_id={id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //高级查询
        public async Task<FenYe<SaleReturn_h>> GetAdvancedSaleReturn_h(FenYe<SaleReturn_h> fenYe, string? code, string? starttotal, string? endtotal, string? startdate, string? enddate, string? startnum, string? endnum, int? status, int? cust, string? custlink, string? salecode, int? dep, int? user, string? createname, string? startcreatetime, string? endcreatetime, string? approvername, string? startapprovertime, string? endapprovertime)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 分页存储过程
                DynamicParameters param = new DynamicParameters();
                param.Add("@pageSize", 10);
                param.Add("@keyName", "create_time DESC");
                param.Add("@tabelName", "vw_salereturn");
                param.Add("@currentPage", fenYe.CurrentPage);
                param.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);
                //判断条件
                string sqlparam = $" is_delete=0 ";
                if (!string.IsNullOrEmpty(code))
                    sqlparam += $" and salet_code like '%{code}%'";
                if (!string.IsNullOrEmpty(starttotal) || !string.IsNullOrEmpty(endtotal))
                    sqlparam += $" and  salet_total between '{starttotal}' and '{endtotal}'";
                if (!string.IsNullOrEmpty(startdate) || !string.IsNullOrEmpty(enddate))
                    sqlparam += $" and  salet_date between '{startdate}' and '{enddate}'";
                if (!string.IsNullOrEmpty(startnum) || !string.IsNullOrEmpty(endnum))
                    sqlparam += $" and  salet_num between '{startnum}' and '{endnum}'";
                if (status != null && status != 0)
                    sqlparam += $" and  salet_status={status}";
                if (cust != null && cust != 0)
                    sqlparam += $" and  cust_id={cust}";
                if (!string.IsNullOrEmpty(custlink))
                    sqlparam += $" and cust_linkman like '%{custlink}%'";
                if (!string.IsNullOrEmpty(salecode))
                    sqlparam += $" and sale_code like '%{salecode}%'";
                if (dep != null && dep != 0)
                    sqlparam += $" and  sale_depId={dep}";
                if (user != null && user != 0)
                    sqlparam += $" and  sale_userId={user}";
                if (!string.IsNullOrEmpty(createname))
                    sqlparam += $" and create_name like '%{createname}%'";
                if (!string.IsNullOrEmpty(startcreatetime) || !string.IsNullOrEmpty(endcreatetime))
                    sqlparam += $" and  create_time between '{startcreatetime}' and '{endcreatetime}'";
                if (!string.IsNullOrEmpty(approvername))
                    sqlparam += $" and approver_name like '%{approvername}%'";
                if (!string.IsNullOrEmpty(startapprovertime) || !string.IsNullOrEmpty(endapprovertime))
                    sqlparam += $" and  approver_time between '{startapprovertime}' and '{endapprovertime}'";

                param.Add("@where", sqlparam);

                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                List<SaleReturn_h> list = sqlConnection.Query<SaleReturn_h>(sql, param).ToList();
                fenYe.List = list;
                fenYe.Rows = param.Get<int>("@rows");
                return fenYe;
            }
        }


        //查询所有单据
        public async Task<List<SaleReturn_h>> GetAllSaleReturn_h()
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = "select * from [dbo].[ord_sale_return_h] where is_delete=0 and (salet_status=4 or salet_status=5)";
                return sqlConnection.Query<SaleReturn_h>(sql).ToList();
            }
        }


        //分页查询
        public async Task<FenYe<SaleReturn_h>> GetSaleReturn_h(FenYe<SaleReturn_h> fenYe, string? code, int? status, int? cust, string? starttime, string? endtime)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 分页存储过程
                DynamicParameters param = new DynamicParameters();
                param.Add("@pageSize", 10);
                param.Add("@keyName", "create_time DESC");
                param.Add("@tabelName", "vw_salereturn");
                param.Add("@currentPage", fenYe.CurrentPage);
                param.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);
                //判断条件
                string sqlparam = $" is_delete=0 ";
                if (!string.IsNullOrEmpty(code))
                    sqlparam += $" and salet_code like '%{code}%'";
                if (cust != 0 && cust != null)
                    sqlparam += $" and cust_id ={cust}";
                if (status != null && status != 0)
                    sqlparam += $" and  salet_status={status}";
                if (!string.IsNullOrEmpty(starttime) || !string.IsNullOrEmpty(endtime))
                    sqlparam += $" and  salet_date between '{starttime}' and '{endtime}'";

                param.Add("@where", sqlparam);

                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                List<SaleReturn_h> list = sqlConnection.Query<SaleReturn_h>(sql, param).ToList();
                fenYe.List = list;
                fenYe.Rows = param.Get<int>("@rows");
                return fenYe;
            }
        }


        //根据code查询信息
        public async Task<SaleReturn_h> GetSaleReturn_hByCode(string code)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"select * from [dbo].[ord_sale_return_h] where salet_code='{code}'";
                return await sqlConnection.QueryFirstAsync<SaleReturn_h>(sql);
            }
        }


        //根据id查询信息
        public async Task<SaleReturn_h> GetSaleReturn_hById(int saletid)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"select * from [dbo].[ord_sale_return_h] where salet_id={saletid}";
                return await sqlConnection.QueryFirstAsync<SaleReturn_h>(sql);
            }
        }


        //根据关联单号查询退货记录
        public async Task<List<SaleReturn_h>> GetSaleReturn_hs(string salecode)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select * from [dbo].[ord_sale_return_h] where sale_code='{salecode}'";
                return sqlConnection.Query<SaleReturn_h>(sql).ToList();
            }
        }


        //更新入库数量及状态
        public async Task<int> UpdateNum(int saletid, int oknum, int nonum, int statu)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[ord_sale_return_h] set salet_instockOK_num={oknum},salet_instockNO_num={nonum},salet_status={statu} where salet_id={saletid}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //修改
        public async Task<int> UpdateSaleReturn_h(SaleReturn_h saleReturn_H)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[ord_sale_return_h] set salet_date='{saleReturn_H.salet_date}', sale_id={saleReturn_H.sale_id}, sale_code='{saleReturn_H.sale_code}', cust_id={saleReturn_H.cust_id}, cust_name='{saleReturn_H.cust_name}', cust_linkman='{saleReturn_H.cust_linkman}', cust_linkphone='{saleReturn_H.cust_linkphone}', 
                                sale_depId={saleReturn_H.sale_depId}, sale_depName='{saleReturn_H.sale_depName}', sale_userId={saleReturn_H.sale_userId}, sale_userName='{saleReturn_H.sale_userName}', salet_reason='{saleReturn_H.salet_reason}', salet_categories={saleReturn_H.salet_categories}, salet_num={saleReturn_H.salet_num}, salet_total='{saleReturn_H.salet_total}',
                                salet_instockOK_num={saleReturn_H.salet_instockOK_num}, salet_instockNO_num={saleReturn_H.salet_instockNO_num}, salet_status={saleReturn_H.salet_status}, update_name='{saleReturn_H.update_name}', update_time='{saleReturn_H.update_time}', remark='{saleReturn_H.remark}' where salet_id={saleReturn_H.salet_id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }


        //撤销
        public async Task<int> UpdateStatus(int saletid, int status)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[ord_sale_return_h] set salet_status={status} where salet_id={saletid}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }
    }
}
